﻿using HotelManagementClient.Model;
using HotelManagementClient.ViewModel;
using System;
using System.Collections.Generic;
using System.Data.SQLite;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Documents;
using UnityEngine;
using static System.Windows.Forms.VisualStyles.VisualStyleElement.StartPanel;

namespace HotelManagementClient.Utlis
{
    public class SqlLiteUtil
    {
        private static SQLiteConnection connection;


        /// <summary>
        /// 创建数据库
        /// </summary>
        public static void InitSqlite()
        {
            string connectionString = "Data Source=database.db;Version=3;";
            SQLiteConnection sqlConnection = new SQLiteConnection(connectionString);
            sqlConnection.Open();

            connection = sqlConnection;
        }

        /// <summary>
        /// 创建群组表
        /// id              自增长编号
        /// groupCode       群组编号
        /// groupName       群组名称
        /// groupAvatar     群组图片
        /// createUser      群组创建人
        /// createTime      群组创建时间
        /// status          群组状态(0正常 1异常)
        /// hotelId         酒店编号
        /// </summary>
        public static void CreateChatGroup()
        {
            if (null != connection)
            {
                string createTableQuery = "CREATE TABLE IF NOT EXISTS ChatGroup (id INTEGER PRIMARY KEY AUTOINCREMENT,groupCode Text NOT NULL,groupName Text NOT NULL,groupAvatar Text NOT NULL,createUser TEXT NOT NULL,createTime Text NOT NULL,status TEXT NOT NULL,hotelId TEXT NOT NULL)";
                SQLiteCommand command = new SQLiteCommand(createTableQuery, connection);
                command.ExecuteNonQuery();
            }
        }

        /// <summary>
        /// 禁用群组信息
        /// </summary>
        /// <param name="groupCode">群组编号</param>
        public static void DisableGroup(string groupCode)
        {
            string updateDataQuery = "UPDATE ChatGroup SET status = 1 WHERE groupCode = @groupCode;";
            SQLiteCommand updateCommand = new SQLiteCommand(updateDataQuery, connection);
            updateCommand.Parameters.AddWithValue("@groupCode", groupCode);
            updateCommand.ExecuteNonQuery();
        }


        /// <summary>
        /// 数据表更新
        /// 2024年3月20日 1、群组表增加状态status(0正常 1异常)
        /// </summary>
        public static void UpdateDatabase()
        {
            if (null != connection)
            {
                //2024年3月20日 群组增加状态字段
                SQLiteCommand queryChatGropStatus = new SQLiteCommand("select sql from sqlite_master where type = 'table' and name = 'ChatGroup' and sql like '%status%'", connection);
                if (queryChatGropStatus != null)
                {
                    SQLiteDataReader readerChatGroupStatus = queryChatGropStatus.ExecuteReader();
                    if (!readerChatGroupStatus.HasRows)
                    {
                        //增加群组表中的字段status
                        SQLiteCommand addChatGropStatus = new SQLiteCommand("alter table ChatGroup add column 'status' Text default '0'", connection);
                        addChatGropStatus.ExecuteNonQuery();
                    }
                    readerChatGroupStatus.Close();
                }

                //2024年3月21日 群组增加酒店编号字段
                SQLiteCommand queryChatGropHotelId = new SQLiteCommand("select sql from sqlite_master where type = 'table' and name = 'ChatGroup' and sql like '%hotelId%'", connection);
                if (queryChatGropHotelId != null)
                {
                    SQLiteDataReader readerChatGroupHotelId = queryChatGropHotelId.ExecuteReader();
                    if (!readerChatGroupHotelId.HasRows)
                    {
                        //增加群组表中的字段hotelId
                        SQLiteCommand addChatGroupHotelId = new SQLiteCommand("alter table ChatGroup add column 'hotelId' INTEGER NOT NULL default " + MainWindow.login_hotel_id, connection);
                        addChatGroupHotelId.ExecuteNonQuery();
                    }
                    readerChatGroupHotelId.Close();
                }

                //2024年3月22日 群内容增加消息类型字段(0文本 1图片)
                SQLiteCommand queryChatGropContentMsgType = new SQLiteCommand("select sql from sqlite_master where type = 'table' and name = 'ChatGroupContent' and sql like '%msgType%'", connection);
                if (queryChatGropContentMsgType != null)
                {
                    SQLiteDataReader readerChatGroupContentMsgType = queryChatGropContentMsgType.ExecuteReader();
                    if (!readerChatGroupContentMsgType.HasRows)
                    {
                        //增加msgType字段(0文本 1图片)
                        SQLiteCommand addChatGroupContentMsgType = new SQLiteCommand("alter table ChatGroupContent add column 'msgType' INTEGER NOT NULL default '0'", connection);
                        addChatGroupContentMsgType.ExecuteNonQuery();
                    }
                    readerChatGroupContentMsgType.Close();
                }

                //2024年4月3日 个人聊天内容加入消息类型字段(0文本 1图片)
                SQLiteCommand queryChatContentMsgType = new SQLiteCommand("select sql from sqlite_master where type = 'table' and name = 'ChatContent' and sql like '%msgType%'", connection);
                if (queryChatContentMsgType != null)
                {
                    SQLiteDataReader readerChatContentMsgType = queryChatContentMsgType.ExecuteReader();
                    if (!readerChatContentMsgType.HasRows)
                    {
                        //增加msgType字段(0文本 1图片)
                        SQLiteCommand addChatContentMsgType = new SQLiteCommand("alter table ChatContent add column 'msgType' INTEGER NOT NULL default '0'", connection);
                        addChatContentMsgType.ExecuteNonQuery();
                    }
                    readerChatContentMsgType.Close();
                }
            }
        }

        /// <summary>
        /// 创建群组用户表
        /// id          自增长编号
        /// groupCode   群组编号
        /// userName    用户编号
        /// nickName    用户昵称
        /// avatar      用户头像
        /// </summary>
        public static void CreateChatGroupUser()
        {
            if (null != connection)
            {
                string createTableQuery = "CREATE TABLE IF NOT EXISTS ChatGroupUser (id INTEGER PRIMARY KEY AUTOINCREMENT,groupCode Text NOT NULL,userName TEXT NOT NULL,nickName TEXT NOT NULL,avatar TEXT NOT NULL)";
                SQLiteCommand command = new SQLiteCommand(createTableQuery, connection);
                command.ExecuteNonQuery();
            }
        }

        /// <summary>
        /// 检查群组信息是否存在
        /// </summary>
        /// <param name="groupCode">群组编号</param>
        /// <param name="hotelId">酒店编号</param>
        /// <returns></returns>
        public static bool CheckChatGroup(string groupCode, int hotelId)
        {
            if (null != connection)
            {
                StringBuilder strSql = new StringBuilder();
                strSql.Append("SELECT * FROM ChatGroup where  groupCode='" + groupCode + "' and hotelId=" + hotelId);
                SQLiteCommand command = new SQLiteCommand(strSql.ToString(), connection);
                SQLiteDataReader reader = command.ExecuteReader();
                if (reader.HasRows)
                {
                    Console.WriteLine("数据已经存在");
                    reader.Close();
                    return true;
                }
                else
                {
                    Console.WriteLine("数据不存在");
                    reader.Close();
                    return false;
                }
            }
            return false;
        }

        /// <summary>
        /// 保存群组信息
        /// </summary>
        /// <param name="groupCode">群编号</param>
        /// <param name="groupName">群名称</param>
        /// <param name="groupAvatar">群图片</param>
        /// <param name="createUser">群创建者</param>
        /// <param name="createTime">群创建时间</param>
        /// <param name="hotelId">酒店编号</param>
        public static void SaveChatGroup(string groupCode, string groupName, string groupAvatar, string createUser, string createTime, int hotelId)
        {
            if (null != connection)
            {
                string insertDataQuery = "INSERT INTO ChatGroup(groupCode,groupName,groupAvatar,createUser,createTime,status,hotelId) VALUES (@groupCode,@groupName,@groupAvatar,@createUser,@createTime,@status,@hotelId)";
                SQLiteCommand command = new SQLiteCommand(insertDataQuery, connection);
                command.Parameters.AddWithValue("@groupCode", groupCode);
                command.Parameters.AddWithValue("@groupName", groupName);
                command.Parameters.AddWithValue("@groupAvatar", groupAvatar);
                command.Parameters.AddWithValue("@createUser", createUser);
                command.Parameters.AddWithValue("@createTime", createTime);
                command.Parameters.AddWithValue("@status", "0");
                command.Parameters.AddWithValue("@hotelId", hotelId);
                command.ExecuteNonQuery();
            }
        }


        /// <summary>
        /// 检查用户是否在群组中
        /// </summary>
        /// <param name="userName">用户</param>
        /// <param name="groupCode">群编号</param>
        /// <returns></returns>
        public static bool CheckChatGroupUser(string userName, string groupCode)
        {
            if (null != connection)
            {
                StringBuilder strSql = new StringBuilder();
                strSql.Append("SELECT * FROM ChatGroupUser where  groupCode='" + groupCode + "' and userName='" + userName + "'");

                SQLiteCommand command = new SQLiteCommand(strSql.ToString(), connection);

                SQLiteDataReader reader = command.ExecuteReader();

                if (reader.HasRows)
                {
                    Console.WriteLine("数据已经存在");
                    reader.Close();
                    return true;
                }
                else
                {
                    Console.WriteLine("数据不存在");
                    reader.Close();
                    return false;
                }
            }

            return false;
        }


        /// <summary>
        /// 保存群组用户信息
        /// </summary>
        /// <param name="groupCode">群编号</param>
        /// <param name="userName">用户编号</param>
        /// <param name="nickName">用户昵称</param>
        /// <param name="avatar">用户头像</param>
        public static void SaveChatGroupUser(string groupCode, string userName, string nickName, string avatar)
        {
            if (null != connection)
            {
                string insertDataQuery = "INSERT INTO ChatGroupUser(groupCode,userName,nickName,avatar) VALUES (@groupCode,@userName,@nickName,@avatar)";
                SQLiteCommand command = new SQLiteCommand(insertDataQuery, connection);
                command.Parameters.AddWithValue("@groupCode", groupCode);
                command.Parameters.AddWithValue("@userName", userName);
                command.Parameters.AddWithValue("@nickName", nickName);
                command.Parameters.AddWithValue("@avatar", avatar);
                command.ExecuteNonQuery();
            }
        }

        /// <summary>
        /// 获取群组中的成员列表
        /// </summary>
        /// <param name="groupCode">群组编号</param>
        /// <returns></returns>
        public static List<GroupUserItem> QueryGroupUser(string groupCode)
        {
            List<GroupUserItem> groupUserItems = new List<GroupUserItem>();
            if (null != connection)
            {
                StringBuilder strSql = new StringBuilder();

                strSql.Append("SELECT * FROM ChatGroupUser where  groupCode='" + groupCode + "'");

                SQLiteCommand command = new SQLiteCommand(strSql.ToString(), connection);

                if (command != null)
                {
                    SQLiteDataReader reader = command.ExecuteReader();

                    while (reader.Read())
                    {
                        string _userName = (string)reader["userName"];
                        string _nickName = (string)reader["nickName"];
                        string _avatar = (string)reader["avatar"];
                        groupUserItems.Add(new GroupUserItem(_userName, _nickName, _avatar));
                    }
                    reader.Close();
                }
            }
            return groupUserItems;
        }


        /// <summary>
        /// 创建用户表
        /// id          自增长ID
        /// userType    用户分类(0预定用户、1在住用户、2离店客户、3意向客户、4商城会员、5协议单位、6门店职员、7平台客服)
        /// userName    用户编号
        /// nickName    用户昵称
        /// avatar      用户头像
        /// createTime  创建时间
        /// </summary>
        public static void CreateChatUser()
        {
            if (null != connection)
            {
                string createTableQuery = "CREATE TABLE IF NOT EXISTS ChatUser (id INTEGER PRIMARY KEY AUTOINCREMENT, userType INTEGER,userName TEXT NOT NULL,nickName TEXT NOT NULL,avatar TEXT NOT NULL, createTime TEXT NOT NULL);";
                SQLiteCommand command = new SQLiteCommand(createTableQuery, connection);
                command.ExecuteNonQuery();
            }
        }

        /// <summary>
        /// 保存用户信息
        /// </summary>
        /// <param name="userType">用户类型</param>
        /// <param name="userName">用户编号</param>
        /// <param name="nickName">用户昵称</param>
        /// <param name="avatar">用户头像</param>
        public static void SaveChatUser(int userType, string userName, string nickName, string avatar)
        {
            if (null != connection)
            {
                string insertDataQuery = "INSERT INTO ChatUser (UserType,UserName,NickName,Avatar,createTime) VALUES (@userType,@userName,@nickName,@avatar,@createTime);";
                SQLiteCommand command = new SQLiteCommand(insertDataQuery, connection);
                command.Parameters.AddWithValue("@userType", userType);
                command.Parameters.AddWithValue("@userName", userName);
                command.Parameters.AddWithValue("@nickName", nickName);
                command.Parameters.AddWithValue("@avatar", avatar);
                command.Parameters.AddWithValue("@createTime", DateTime.Now);

                command.ExecuteNonQuery();
            }
        }

        /// <summary>
        /// 检查用户是否存在
        /// </summary>
        /// <param name="userType">用户类型</param>
        /// <param name="userName">用户编号</param>
        /// <param name="avatar">用户头像</param>
        /// <returns></returns>
        public static bool CheckChatUser(int userType, string userName, string avatar)
        {
            if (null != connection)
            {
                StringBuilder strSql = new StringBuilder();
                strSql.Append("SELECT * FROM ChatUser where  userType=" + userType + " and userName='" + userName + "' limit 1");

                SQLiteCommand command = new SQLiteCommand(strSql.ToString(), connection);

                SQLiteDataReader reader = command.ExecuteReader();

                if (reader.HasRows)
                {
                    //更新头像信息
                    while (reader.Read())
                    {

                        int id = Convert.ToInt32(reader["id"]);
                        string updateDataQuery = "UPDATE ChatUser SET avatar = @avatar WHERE id = @id;";
                        SQLiteCommand updateCommand = new SQLiteCommand(updateDataQuery, connection);
                        if (ImgUtil.RemoteFileExists(avatar))
                        {
                            updateCommand.Parameters.AddWithValue("@avatar", avatar);
                        }
                        else
                        {
                            updateCommand.Parameters.AddWithValue("@avatar", "/Images/user.png");
                        }
                        updateCommand.Parameters.AddWithValue("@id", id);
                        updateCommand.ExecuteNonQuery();
                    }

                    Console.WriteLine("数据已经存在");
                    reader.Close();
                    return true;
                }
                else
                {
                    Console.WriteLine("数据不存在");
                    reader.Close();
                    return false;
                }
            }

            return false;
        }

        /// <summary>
        /// 查询单用户信息
        /// </summary>
        /// <param name="userType">用户类型</param>
        /// <param name="userName">用户编号</param>
        /// <returns></returns>
        public static ChatUser QueryOneChatUser(int userType, string userName)
        {
            ChatUser user = new ChatUser();
            if (null != connection)
            {
                StringBuilder strSql = new StringBuilder();
                strSql.Append("SELECT * FROM ChatUser where  userType=" + userType + " and userName='" + userName + "' limit 1");

                SQLiteCommand command = new SQLiteCommand(strSql.ToString(), connection);

                SQLiteDataReader reader = command.ExecuteReader();

                while (reader.Read())
                {
                    int _id = Convert.ToInt32(reader["id"]);
                    int _userType = Convert.ToInt32(reader["userType"]);
                    string _userName = (string)reader["userName"];
                    string _nickName = (string)reader["nickName"];
                    string _avatar = (string)reader["avatar"];

                    user = new ChatUser(_id, _userType, _userName, _nickName, _avatar);
                }

                reader.Close();
            }

            return user;
        }

        /// <summary>
        /// 根据用户类型查询用户列表
        /// </summary>
        /// <param name="userType">用户类型</param>
        /// <returns></returns>
        public static List<ChatUser> QueryChatUser(int userType)
        {
            List<ChatUser> list = new List<ChatUser>();
            if (null != connection)
            {
                StringBuilder strSql = new StringBuilder();
                strSql.Append("SELECT * FROM ChatUser where  userType= " + userType);


                SQLiteCommand command = new SQLiteCommand(strSql.ToString(), connection);

                if (command != null)
                {
                    SQLiteDataReader reader = command.ExecuteReader();

                    while (reader.Read())
                    {
                        int _id = Convert.ToInt32(reader["id"]);
                        int _userType = Convert.ToInt32(reader["userType"]);
                        string _userName = (string)reader["userName"];
                        string _nickName = (string)reader["nickName"];
                        string _avatar = (string)reader["avatar"];

                        list.Add(new ChatUser(_id, _userType, _userName, _nickName, _avatar));
                    }
                    reader.Close();
                }

            }
            return list;
        }

        /// <summary>
        /// 根据用户编号查询群组列表
        /// </summary>
        /// <param name="username">用户编号</param>
        /// <param name="hotelId">酒店编号</param>
        /// <returns></returns>
        public static List<ChatGroup> QueryChatGroup(string username, int hotelId)
        {
            List<ChatGroup> list = new List<ChatGroup>();
            if (null != connection)
            {
                StringBuilder strSql = new StringBuilder();
                strSql.Append("SELECT b.* FROM ChatGroupUser a INNER JOIN ChatGroup b on a.groupCode=b.groupCode where a.userName='" + username + "' and b.hotelId= " + hotelId);

                SQLiteCommand command = new SQLiteCommand(strSql.ToString(), connection);

                if (command != null)
                {
                    SQLiteDataReader reader = command.ExecuteReader();

                    while (reader.Read())
                    {
                        int _id = Convert.ToInt32(reader["id"]);//自增长ID
                        string _groupCode = (string)reader["groupCode"];//群组编号
                        string _groupName = (string)reader["groupName"];//群组名称
                        string _groupAvatar = (string)reader["groupAvatar"];//群组图片
                        string _createUser = (string)reader["createUser"];//创建用户
                        string _createTime = (string)reader["createTime"];//创建时间
                        string _status = (string)reader["status"];//群组状态(0正常 1异常)
                        int _hotelId = Convert.ToInt32(reader["hotelId"]);//酒店编号
                        list.Add(new ChatGroup(_id, _groupCode, _groupName, _groupAvatar, _createUser, _createTime, _status, _hotelId, "#1c2125"));
                    }
                    reader.Close();
                }

            }
            return list;
        }

        /// <summary>
        /// 创建个人聊天内容信息表
        /// id              自增长ID
        /// sendUser        发送者
        /// recipientUser   接收者
        /// createTime      创建时间
        /// chatMsg         消息内容
        /// </summary>
        public static void CreateChatContent()
        {
            if (null != connection)
            {
                string createTableQuery = "CREATE TABLE IF NOT EXISTS ChatContent (id INTEGER PRIMARY KEY AUTOINCREMENT, sendUser TEXT NOT NULL,recipientUser TEXT NOT NULL, createTime TEXT NOT NULL, chatMsg TEXT NOT NULL);";
                SQLiteCommand command = new SQLiteCommand(createTableQuery, connection);
                command.ExecuteNonQuery();
            }
        }

        /// <summary>
        /// 创建群组聊天内容表
        /// id          自增长ID
        /// groupCode   群组编号
        /// sendUser    消息发送者
        /// nickName    发送者昵称
        /// avatar      发送者头像
        /// createTime  发送时间
        /// chatMsg     消息内容
        /// msgType     消息类型(0文本 1图片)
        /// </summary>
        public static void CreateChatGroupContent()
        {
            if (null != connection)
            {
                string createTableQuery = "CREATE TABLE IF NOT EXISTS ChatGroupContent (id INTEGER PRIMARY KEY AUTOINCREMENT, groupCode TEXT NOT NULL,sendUser TEXT NOT NULL,nickName TEXT NOT NULL,avatar TEXT NOT NULL,createTime TEXT NOT NULL, chatMsg TEXT NOT NULL,msgType TEXT NOT NULL);";
                SQLiteCommand command = new SQLiteCommand(createTableQuery, connection);
                command.ExecuteNonQuery();
            }
        }

        /// <summary>
        /// 保存群消息内容
        /// </summary>
        /// <param name="groupCode">群编号</param>
        /// <param name="sendUser">消息发送者编号</param>
        /// <param name="nickName">消息发送者昵称</param>
        /// <param name="avatar">消息发送者头像</param>
        /// <param name="chatMsg">消息内容</param>
        /// <param name="createTime">消息发送时间</param>
        /// <param name="msgType">消息类型(0文本 1图片)</param>
        public static void SaveChatGroupContent(string groupCode, string sendUser, string nickName, string avatar, string chatMsg, string createTime, string msgType)
        {
            if (null != connection)
            {
                string insertDataQuery = "INSERT INTO ChatGroupContent (groupCode,sendUser,nickName,avatar,chatMsg,createTime,msgType) VALUES (@groupCode,@sendUser,@nickName,@avatar,@chatMsg,@createTime,@msgType);";
                SQLiteCommand command = new SQLiteCommand(insertDataQuery, connection);
                command.Parameters.AddWithValue("@groupCode", groupCode);
                command.Parameters.AddWithValue("@sendUser", sendUser);
                command.Parameters.AddWithValue("@nickName", nickName);
                command.Parameters.AddWithValue("@avatar", avatar);
                command.Parameters.AddWithValue("@chatMsg", chatMsg);
                command.Parameters.AddWithValue("@createTime", createTime);
                command.Parameters.AddWithValue("@msgType", msgType);
                command.ExecuteNonQuery();
            }
        }

        /// <summary>
        /// 检查消失是否存在
        /// </summary>
        /// <param name="groupCode">群组编号</param>
        /// <param name="groupSendUser">消息发送者</param>
        /// <param name="groupMsg">消息内容</param>
        /// <param name="groupCreateTime">消息发送时间</param>
        /// <returns></returns>
        public static bool CheckChatGroupContent(string groupCode, string groupSendUser, string groupMsg, string groupCreateTime)
        {
            if (null != connection)
            {
                StringBuilder strSql = new StringBuilder();
                strSql.Append("SELECT * FROM ChatGroupContent WHERE groupCode='" + groupCode + "' and sendUser='" + groupSendUser + "' and chatMsg='" + groupMsg + "' and createTime='" + groupCreateTime + "' LIMIT 1");
                SQLiteCommand command = new SQLiteCommand(strSql.ToString(), connection);

                SQLiteDataReader reader = command.ExecuteReader();

                if (reader.HasRows)
                {
                    Console.WriteLine("数据已经存在");
                    reader.Close();
                    return true;
                }
                else
                {
                    Console.WriteLine("数据不存在");
                    reader.Close();
                    return false;
                }
            }
            return false;
        }

        /// <summary>
        /// 保存个人聊天消息内容
        /// </summary>
        /// <param name="sendUser">发送者</param>
        /// <param name="recipientUser">接收者</param>
        /// <param name="chatMsg">消息内容</param>
        public static void SaveChatContent(string sendUser, string recipientUser, string chatMsg, string createTime)
        {
            if (null != connection)
            {
                string insertDataQuery = "INSERT INTO ChatContent (sendUser,recipientUser,createTime,chatMsg) VALUES (@sendUser,@recipientUser,@createTime,@chatMsg);";
                SQLiteCommand command = new SQLiteCommand(insertDataQuery, connection);
                command.Parameters.AddWithValue("@sendUser", sendUser);
                command.Parameters.AddWithValue("@recipientUser", recipientUser);
                command.Parameters.AddWithValue("@createTime", createTime);
                command.Parameters.AddWithValue("@chatMsg", chatMsg);
                command.ExecuteNonQuery();
            }
        }

        /// <summary>
        /// 查询个人消息内容
        /// </summary>
        /// <param name="sendUser">发送者</param>
        /// <param name="recipientUser">接收者</param>
        /// <returns></returns>
        public static List<ChatContent> QueryChatContent(string sendUser, string recipientUser)
        {
            List<ChatContent> list = new List<ChatContent>();
            if (null != connection)
            {
                StringBuilder strSql = new StringBuilder();

                strSql.Append("SELECT * FROM (SELECT * FROM ChatContent where sendUser='" + sendUser + "' and recipientUser='" + recipientUser + "'  UNION ALL SELECT * FROM ChatContent where sendUser='" + recipientUser + "' and recipientUser='" + sendUser + "' ) AA ORDER BY  AA.id");

                SQLiteCommand command = new SQLiteCommand(strSql.ToString(), connection);

                if (command != null)
                {
                    SQLiteDataReader reader = command.ExecuteReader();

                    while (reader.Read())
                    {
                        int _id = Convert.ToInt32(reader["id"]);
                        string _sendUser = (string)reader["sendUser"];
                        string _recipientUser = (string)reader["recipientUser"];
                        string _createTime = (string)reader["createTime"];
                        string _chatMsg = (string)reader["chatMsg"];
                        string _chatMsgType = Convert.ToString(reader["msgType"]);

                        list.Add(new ChatContent(_id, _sendUser, _recipientUser, _createTime, _chatMsg, _chatMsgType));
                    }
                    reader.Close();
                }
            }
            return list;
        }

        /// <summary>
        /// 获取单聊的最后一条消息的时间
        /// </summary>
        /// <param name="sendUser"></param>
        /// <param name="recipientUser"></param>
        /// <returns></returns>
        public static string QueryChatContentByLastTime(string sendUser, string recipientUser)
        {
            string createTime = "";
            if (null != connection)
            {
                StringBuilder strSql = new StringBuilder();

                strSql.Append("SELECT * FROM (SELECT * FROM ChatContent where sendUser='" + sendUser + "' and recipientUser='" + recipientUser + "'  UNION ALL SELECT * FROM ChatContent where sendUser='" + recipientUser + "' and recipientUser='" + sendUser + "' ) AA ORDER BY  AA.id DESC LIMIT 1");

                SQLiteCommand command = new SQLiteCommand(strSql.ToString(), connection);

                if (command != null)
                {
                    SQLiteDataReader reader = command.ExecuteReader();

                    while (reader.Read())
                    {
                        createTime = (string)reader["createTime"];
                    }
                    reader.Close();
                }
            }
            return createTime;
        }


        /// <summary>
        /// 查询群组消息内容
        /// </summary>
        /// <param name="username">用户编号</param>
        /// <param name="groupCode">群编号</param>
        /// <returns></returns>
        public static List<ChatGroupContent> QueryChatGroupContent(string username, string groupCode)
        {
            List<ChatGroupContent> list = new List<ChatGroupContent>();
            //检测登录用户是否在群组中
            if (SqlLiteUtil.CheckChatGroupUser(username, groupCode) && null != connection)
            {
                StringBuilder strSql = new StringBuilder();

                //strSql.Append("SELECT * FROM ChatGroupContent WHERE groupCode ='"+ groupCode + "' ORDER BY createTime DESC LIMIT 0,100");
                strSql.Append("select * from (select * from ChatGroupContent where groupCode ='" + groupCode + "' order by createTime desc limit 50) as a order by a.createTime");
                //select* from(select* from reg_user order by user_id desc limit 10) as a order by a.user_id
                SQLiteCommand command = new SQLiteCommand(strSql.ToString(), connection);

                if (command != null)
                {
                    SQLiteDataReader reader = command.ExecuteReader();

                    while (reader.Read())
                    {
                        int _id = Convert.ToInt32(reader["id"]);
                        string _groupCode = (string)reader["groupCode"];
                        string _sendUser = (string)reader["sendUser"];
                        string _nickName = (string)reader["nickName"];
                        string _avatar = (string)reader["avatar"];
                        string _createTime = (string)reader["createTime"];
                        string _chatMsg = (string)reader["chatMsg"];
                        string _chatMsgType = Convert.ToString(reader["msgType"]);
                        list.Add(new ChatGroupContent(_id, _groupCode, _sendUser, _nickName, _avatar, _chatMsg, _createTime, _chatMsgType));

                    }
                    reader.Close();
                }
            }
            return list;
        }

        /// <summary>
        /// 获取群组的最后一条消息的时间
        /// </summary>
        /// <param name="username">用户编号</param>
        /// <param name="groupCode">群编号</param>
        /// <returns></returns>
        public static string QueryChatGroupContentByLastTime(string username, string groupCode)
        {
            string createTime = "";
            //检测登录用户是否在群组中
            if (SqlLiteUtil.CheckChatGroupUser(username, groupCode) && null != connection)
            {
                StringBuilder strSql = new StringBuilder();

                strSql.Append("SELECT * FROM ChatGroupContent WHERE groupCode ='" + groupCode + "' ORDER BY createTime DESC LIMIT 1 ");

                SQLiteCommand command = new SQLiteCommand(strSql.ToString(), connection);

                if (command != null)
                {
                    SQLiteDataReader reader = command.ExecuteReader();

                    while (reader.Read())
                    {
                        createTime = (string)reader["createTime"];

                    }
                    reader.Close();
                }
            }
            return createTime;
        }

        //更新消息内容
        public static void ChangeChatContent()
        {

            string updateDataQuery = "UPDATE MyTable SET Name = @newName WHERE id = @id;";
            SQLiteCommand updateCommand = new SQLiteCommand(updateDataQuery, connection);
            updateCommand.Parameters.AddWithValue("@newName", "Jane Smith");
            updateCommand.Parameters.AddWithValue("@id", 1);
            updateCommand.ExecuteNonQuery();

            string deleteDataQuery = "DELETE FROM MyTable WHERE id = @id;";
            SQLiteCommand deleteCommand = new SQLiteCommand(deleteDataQuery, connection);
            deleteCommand.Parameters.AddWithValue("@id", 2);
            deleteCommand.ExecuteNonQuery();

        }
    }
}